﻿CREATE PROCEDURE [maint].[GetDeviceHeader]
@DbName [sysname], @DeviceName [sysname], @OutputTableName NVARCHAR (36) OUTPUT, @OutputDiagnostics BIT=0
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC int;
	SET @RC = 0;
	
	IF(@OutputDiagnostics = 1)
		BEGIN
			PRINT OBJECT_NAME(@@PROCID)
			PRINT N'	@DbName = N''' + ISNULL(@DbName,N'NULL') + ''''
			PRINT N'	@DeviceName = N''' + ISNULL(@DeviceName,N'NULL') + ''''
		END

	DECLARE @ProductVersion int
	SET @ProductVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS varchar),1)

	--create output table
	SET @OutputTableName = N'##' + REPLACE(CAST(NEWID() AS nvarchar(36)),N'-',N'')

	DECLARE @CreateSql nvarchar(4000)
	SET @CreateSql = N'CREATE TABLE ' + @OutputTableName + N' (
	[BackupName] nvarchar(128),
	[BackupDescription] nvarchar(255),
	[BackupType] smallint,
	[ExpirationDate] datetime,
	[Compressed] bit,
	[Position] smallint,
	[DeviceType] tinyint,
	[UserName] nvarchar(128),
	[ServerName] nvarchar(128),
	[DatabaseName] nvarchar(128),
	[DatabaseVersion] int,
	[DatabaseCreationDate] datetime,
	[BackupSize] numeric(20,0),
	[FirstLSN] numeric(25,0),
	[LastLSN]  numeric(25,0),
	[CheckpointLSN] numeric(25,0),
	[DatabaseBackupLSN] numeric(25,0),
	[BackupStartDate] datetime,
	[BackupFinishDate] datetime,
	[SortOrder] smallint,
	[CodePage] smallint,
	[UnicodeLocaleId] int,
	[UnicodeComparisonStyle] int,
	[CompatibilityLevel] tinyint,
	[SoftwareVendorId] int,
	[SoftwareVersionMajor] int,
	[SoftwareVersionMinor] int,
	[SoftwareVersionBuild] int,
	[MachineName] nvarchar(128),
	[Flags] int,
	[BindingID] uniqueidentifier,
	[RecoveryForkID] uniqueidentifier,
	[Collation] nvarchar(128),
	[FamilyGUID] uniqueidentifier,
	[HasBulkLoggedData] bit,
	[IsSnapshot] bit,
	[IsReadOnly] bit,
	[IsSingleUser] bit,
	[HasBackupChecksums] bit,
	[IsDamaged] bit,
	[BeginsLogChain] bit,
	[HasIncompleteMetaData] bit,
	[IsForceOffline] bit,
	[IsCopyOnly] bit,
	[FirstRecoveryForkID] uniqueidentifier,
	[ForkPointLSN] numeric(25,0) NULL,
	[RecoveryModel] nvarchar(60),
	[DifferentialBaseLSN] numeric(25,0) NULL,
	[DifferentialBaseGUID] uniqueidentifier,
	[BackupTypeDescription] nvarchar(60),
	[BackupSetGUID] uniqueidentifier NULL,
	[CompressedBackupSize] bigint,
	[Containment] tinyint,
	[KeyAlgorithm] nvarchar(32),
	[EncryptorThumbprint] varbinary(20),
	[EncryptorType] nvarchar(32)
)'
	EXEC @RC = sp_executesql @CreateSql;
	IF(@RC <> 0)
		BEGIN
			RAISERROR('Failed to create tempory table for device (%s) header data',16,1,@DeviceName);
			GOTO EXIT_PROC
		END

	--restore device header of first device
	DECLARE @RestoreSql nvarchar(4000)
	SET @RestoreSql = N'INSERT ' + @OutputTableName + N' 
EXEC(''RESTORE HEADERONLY FROM [' + @DeviceName + N'.1] WITH NOUNLOAD'')'

	EXEC @RC = sp_executesql @RestoreSql;
	IF(@RC <> 0)
		BEGIN
			RAISERROR('Failed to restore header for device %s',16,1,@DeviceName);
			GOTO EXIT_PROC
		END

	EXEC @RC = [maint].[InsertDeviceHeader] @DeviceName, @OutputTableName;

EXIT_PROC:
	RETURN(@RC)	
END